import pymysql
import pandas as pd
import asyncio
import time
import datetime
import traceback
import os
import sys
# print(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
# # 添加导包路径
# sys.path.append(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
sys.path.append(os.path.split(os.path.split(sys.path[0])[0])[0])
from multiprocessing import  Process
from bin.openapi import OpenApiBase
from sqlalchemy import create_engine
import smtplib
from email.mime.multipart import MIMEMultipart  # 构建邮件头信息，包括发件人，接收人，标题等
from email.mime.text import MIMEText  # 构建邮件正文，可以是text，也可以是HTML
from email.mime.application import MIMEApplication  # 构建邮件附件，理论上，只要是文件即可，一般是图片，Excel表格，word文件等
from email.header import Header  # 专门构建邮件标题的，这样做，可以支持标题中文

class ad_father:

    def __init__(self):
        # self.op_api = OpenApiBase("https://openapi.lingxing.com", "ak_A77HkbumPXlk3", "6z+ZCTX2z5ctgITSuH+akQ==") 
        app_id_lst = ["ak_A77HkbumPXlk3", "ak_7qAgs8rCJU5NT", "ak_3m9usOzY7aY2L", "ak_3i2oYyXTYN3Zg", "ak_Qgrt70cYFfTr2", 
                  "ak_idSTQA3QeCgY7", "ak_ZZO2mlycJx3X3", "ak_BY78fGw6KLNcs", "ak_NTMVa4r69kYw6", "ak_E1Wf3R1gWXQfq",
                  "ak_XGz3DNLR3Vnwz", "ak_dLKnG6gFZqwXx", "ak_K7CihJHsdNHG8", "ak_NvVdaVSnmsttZ", "ak_lDV4Buvr5CsNH"]
        app_secret = ["6z+ZCTX2z5ctgITSuH+akQ==", "5hnuOnY//jZJj6tK6n+7Kw==", "DiKV/cCfnkwYCBedElDoqg==", 
                  "F81S0zbz235MZQLHvu2evg==", "wuQj0VRnXi4rYue7HjYz8w==", "8+An5Pg3/178R/PrUqRwCA==",
                  "0gZPKaJumYjYQJh3lq2+aA==", "KlNKdEecZHM71MNrGWQMpg==", "Qrsquih6hExGQjbpmz32GA==", 
                  "O5ny9ogIWO5XkFpTDeyGig==", "qw9xOLJ96HpmxDrVFUhOiw==", "Tzw2U86vk7npmNbGja5D2Q==",
                  "oAJ9NKNnRYNf7QWzuxy+Lg==", "fInPhYOxi5ReaP4B5wyGig==", "WsS0Yot4n+MOmNM0KWDWgA=="]
        # app_id_lst = ["ak_CozTHBPoTCu0F", "ak_A77HkbumPXlk3", "ak_7qAgs8rCJU5NT", "ak_3m9usOzY7aY2L", "ak_3i2oYyXTYN3Zg", "ak_Qgrt70cYFfTr2", 
        #           "ak_idSTQA3QeCgY7", "ak_ZZO2mlycJx3X3", "ak_BY78fGw6KLNcs", "ak_NTMVa4r69kYw6", "ak_E1Wf3R1gWXQfq",
        #           "ak_XGz3DNLR3Vnwz", "ak_dLKnG6gFZqwXx", "ak_K7CihJHsdNHG8", "ak_NvVdaVSnmsttZ", "ak_lDV4Buvr5CsNH"]
        # app_secret = ["DV9ClL8C06ZK/T43IsXe5A==", "6z+ZCTX2z5ctgITSuH+akQ==", "5hnuOnY//jZJj6tK6n+7Kw==", "DiKV/cCfnkwYCBedElDoqg==", 
        #           "F81S0zbz235MZQLHvu2evg==", "wuQj0VRnXi4rYue7HjYz8w==", "8+An5Pg3/178R/PrUqRwCA==",
        #           "0gZPKaJumYjYQJh3lq2+aA==", "KlNKdEecZHM71MNrGWQMpg==", "Qrsquih6hExGQjbpmz32GA==", 
        #           "O5ny9ogIWO5XkFpTDeyGig==", "qw9xOLJ96HpmxDrVFUhOiw==", "Tzw2U86vk7npmNbGja5D2Q==",
        #           "oAJ9NKNnRYNf7QWzuxy+Lg==", "fInPhYOxi5ReaP4B5wyGig==", "WsS0Yot4n+MOmNM0KWDWgA=="]
        self.op_api_lst = []
        for i in range(len(app_id_lst)):
            self.op_api_lst.append(OpenApiBase("https://openapi.lingxing.com", app_id_lst[i], app_secret[i]))
        self.access_token = ""
        self.refresh_token = ""

    async def generate_access_token(self):
        resp = await self.op_api.generate_access_token()
        self.access_token = resp.access_token
        self.refresh_token = resp.refresh_token
        assert resp.access_token
        # return resp.dict()
    
    async def refresh_token_handler(self):
        resp = await self.op_api.refresh_token(self.refresh_token)
        self.refresh_token =  resp.refresh_token
        self.access_token = resp.access_token
        assert resp.access_token

    async def check_token(self, data):
        if data['code'] == 0:
            return True
        elif data['code'] in [2001005, 2001003, 2001008, 2001009]:
            await self.generate_access_token()
            return False
        else:
            print(data['code'])
            return False

class db_tools:
    def __init__(self):
        self.engine = create_engine('mysql+pymysql://user_rpa:123456@192.168.8.10:3306/ad_api?charset=utf8mb4')   # 西安数据库
        self.conn = pymysql.connect(user='user_rpa', password='123456', host='192.168.8.10', database='ad_api') # 西安数据库
        # self.engine = create_engine('mysql+pymysql://user_rpa:123456@10.20.30.1:3306/ad_test?charset=utf8mb4')   # 西安测试数据库
        # self.conn = pymysql.connect(user='user_rpa', password='123456', host='10.20.30.1', database='ad_test') # 西安测试数据库
        # self.engine = create_engine('mysql+pymysql://root:123456@192.168.8.200:3306/new_ad?charset=utf8mb4')  # 河津数据库
        # self.conn = pymysql.connect(user='root', password='123456', host='192.168.8.200', database='new_ad') # 河津数据库
        self.cur = self.conn.cursor()
    
    def insert_data(self, data, USER_TABLE_NAME):
        data.to_sql(USER_TABLE_NAME, con=self.engine, index=False, if_exists="append")

class tools:


    @staticmethod
    def get_db():
        db = db_tools()
        return db

    @staticmethod
    def get_sid_accountname(data):
        sid = data['lx_sid']
        account_name = data['store_name']
        return sid, account_name
    @staticmethod
    def get_date_lst(start_date:int, end_date:int):
        # start_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
        # end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
        date_today = datetime.datetime.now()+ datetime.timedelta(days=start_date)
        end_date = date_today + datetime.timedelta(days=end_date)
        date_lst = []
        while date_today != end_date:
            date_lst.append(date_today.strftime("%Y-%m-%d"))
            date_today = date_today+datetime.timedelta(days=-1)
        return date_lst
    
    @staticmethod
    def get_campaign_id_data_sb(db):
        query = f"select campaign_id, profile_id, name, sid, account, portfolio_id  from sb_campaigns_data_api where  state = 'enabled' and DATE(update_time) = (select DATE(max(update_time)) from sb_campaigns_data_api)"
        query_data = pd.read_sql(query, con=db.engine)
        return query_data
    
    @staticmethod
    def get_campaign_id_data_sd(db):
        query = f"select campaign_id, profile_id, name, sid, account, portfolio_id  from sd_campaigns_data_api where  state = 'enabled' and DATE(update_time) = (select DATE(max(update_time)) from sd_campaigns_data_api)"
        query_data = pd.read_sql(query, con=db.engine)
        return query_data
    def get_campaign_id_data_sp(db):
        query = f"select campaign_id, profile_id, name, sid, account, portfolio_id  from sp_campaigns_data_api where  state = 'enabled' and DATE(update_time) = (select DATE(max(update_time)) from sp_campaigns_data_api)"
        query_data = pd.read_sql(query, con=db.engine)
        return query_data

    @staticmethod
    def del_data(table_name_lst, date_str, db):
        for table_name in table_name_lst:
            sql = f"""DELETE FROM {table_name} WHERE (report_date in ({date_str}) ) ;"""
            db.cur.execute(sql)
        db.conn.commit()

    @staticmethod
    def get_account_by_campaign_id( campaign_id, query_data):
        query_data_one = query_data[query_data["campaign_id"] == campaign_id]
        accout_lst = list(query_data_one['account'])
        sid_lst = list(query_data_one['sid'])
        if len(accout_lst)> 0:

            return accout_lst[0], sid_lst[0]
        else:
            return "Null"
    @staticmethod
    def get_engine(database):
        engine = create_engine(f'mysql+pymysql://user1:123456@192.168.8.10:3306/{database}?charset=utf8mb4')
        return engine
    @staticmethod
    def get_cur(database):
        conn = pymysql.connect(user='user1', password='123456', host='192.168.8.10', database=database)
        cur = conn.cursor()
        return conn, cur


    
class email_send:
    # 初始化
    def __init__(self):
        self.df_html = '广告api邮件'
        self.html_msg = ''
        print("邮箱初始化")

    # 读取每个人的邮箱

    # 构造数据
    def data_handler(self, email_info):

        body = \
            f"""
            <body>
                {email_info}
            </body>
            """.format(df_html=self.df_html)
        self.html_msg = "<html>" + body + "</html>"
        print("邮件内容已构造完成")

    # 发送邮箱
    def email_handler(self, header_info):
        # 发件人：
        smtp_server = 'smtp.163.com'
        username = "silence_will@163.com"
        password = 'GPWJCSLYZWQXUNHD'

        sender = username

        msg = MIMEMultipart('related')
        msg['Subject'] = Header(header_info)
        # msg['Subject'] = Header("测试")
        msg["From"] = sender
        msg['To'] = "silence_will@163.com"  # 这里要注意

        content_html = MIMEText(self.html_msg, "html", "utf-8")
        msg.attach(content_html)

        email_client = smtplib.SMTP(smtp_server)
        email_client.login(username, password)
        email_client.sendmail(sender, "silence_will@163.com", msg.as_string())
        print("邮件已发送")
        email_client.quit()

    def run(self, email_info, header_info="广告api邮件"):
        self.data_handler(email_info)
        self.email_handler(header_info)















